package org.misalen.web.database.repository.mysql;

import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.hibernate.transform.Transformers;
import org.misalen.utils.TextUtil;
import org.misalen.web.database.dao.mysql.impl.BaseMySqlDaoImpl;
import org.misalen.web.database.model.mysql.pro.ProVideoInfo;
import org.misalen.web.database.util.PaginationFrom;
import org.springframework.stereotype.Repository;

@Repository
public class ProVideoInfoRepository extends BaseMySqlDaoImpl<ProVideoInfo> {
	@Override
	public PaginationFrom<ProVideoInfo> findPageByBean(ProVideoInfo allclass, PaginationFrom<ProVideoInfo> pagination) {
		Session session = this.openSession();
		String where = getHqlWhere(allclass);
		String sql = "select a.*,u.userName as userName from ProVideoInfo a left join ProOwnerUser u on a.userId=u.primaryKey "
				+ where + " order by addTime desc";
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(new AliasToBeanResultTransformer(allclass.getClass()));
		pagination.setTotal(Long.valueOf(session
				.createSQLQuery(
						"select count(*) from ProVideoInfo a left join ProOwnerUser u on a.userId=u.primaryKey " + where)
				.uniqueResult().toString()));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		@SuppressWarnings("unchecked")
		List<ProVideoInfo> list = query.list();
		pagination.setList(list);
		closeSession(session);
		return pagination;
	}

	public PaginationFrom<ProVideoInfo> findNewtaskList(String type, String belongCity, String userId,
			PaginationFrom<ProVideoInfo> pagination) {
		StringBuffer selectBuffer = new StringBuffer();
		selectBuffer.append(
				"SELECT A.primaryKey,A.videoName,A.videoThumUrl,A.redEnvelopTotal,A.grabThe,CONVERT(sum(CASE WHEN B.userId = '");
		selectBuffer.append(userId);
		selectBuffer.append("' THEN 1 ELSE NULL END),CHAR) isGet,CONVERT(sum(CASE WHEN B.userId = '");
		selectBuffer.append(userId);
		selectBuffer.append("' THEN B.money ELSE NULL END),SIGNED) getMoney");
		selectBuffer.append(" FROM ProVideoInfo A LEFT JOIN ProVideoReward B ON A.primaryKey = B.videoId");
		selectBuffer.append(" WHERE A.timeLimit = 1");
		selectBuffer.append(" and A.state = 'release' and type='");
		selectBuffer.append(type);
		if(belongCity==null){
			selectBuffer.append("' ");
		}else{
			selectBuffer.append("' and (A.areaScope = '" + belongCity + "' or A.areaScope = '全国') ");
		}
		selectBuffer.append(" GROUP BY A.primaryKey ORDER BY A.addTime desc");
		Session session = this.openSession();
		SQLQuery query = session.createSQLQuery(selectBuffer.toString());
		query.setResultTransformer(new AliasToBeanResultTransformer(ProVideoInfo.class));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		StringBuffer countBuffer = new StringBuffer();
		countBuffer.append(
				"  select count(*) from (SELECT count(*) from ProVideoInfo A  WHERE A.timeLimit = 1 AND A.grabThe = 1 and A.state = 'release'");
		countBuffer.append(" and A.state = 'release' and A.type='");
		countBuffer.append(type);
		countBuffer.append("' and (A.areaScope = '" + belongCity + "' or A.areaScope = '全国') ");
		countBuffer.append(" GROUP BY A.primaryKey) T");
		pagination.setTotal(Long.valueOf(session.createSQLQuery(countBuffer.toString()).uniqueResult().toString()));
		@SuppressWarnings("unchecked")
		List<ProVideoInfo> list = query.list();
		pagination.setList(list);
		pagination.setTotal((long) list.size());
		closeSession(session);
		return pagination;
	}

	public ProVideoInfo getValid(String videoId) {
		String hql = "from ProVideoInfo where primaryKey=?0 and timeLimit=?1 and grabThe=?2";
		return get(hql, new Object[] { videoId, TextUtil.booleanToString(true), TextUtil.booleanToString(false) });
	}

	public List<Map<String, Object>> findAllByDriverUserType(String userId, String type) {
		Session session = this.openSession();
		String sql = "SELECT b.videoUrl videoUrl,b.videoThumUrl videoThumUrl,b.primaryKey videoId "
				+ "FROM ProVideoReward a LEFT JOIN ProVideoInfo b ON a.videoId=b.primaryKey  "
				+ " where b.timeLimit='1' AND a.userId='" + userId + "' AND b.type='" + type + "'";
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		@SuppressWarnings("unchecked")
		List<Map<String, Object>> list = query.list();
		closeSession(session);
		return list;
	}

	public PaginationFrom<ProVideoInfo> findListByDriverUser(String userId, String type,
			PaginationFrom<ProVideoInfo> pagination, String timeLimit) {
		StringBuffer selectBuffer = new StringBuffer();
		selectBuffer.append("SELECT A.redEnvelopTotal,A.primaryKey, A.videoName, A.videoThumUrl, A.startDate, A.stopDate,");
		selectBuffer.append(
				"CONVERT(B.money,SIGNED) getMoney, CONVERT(B.playeTime,SIGNED) playeTime, CONVERT(B.playedTime,SIGNED) playedTime FROM ProVideoReward B LEFT JOIN ProVideoInfo A ON A.primaryKey = B.videoId");
		selectBuffer.append(" WHERE B.userId='");
		selectBuffer.append(userId);
		selectBuffer.append("' and A.type='");
		selectBuffer.append(type);
		selectBuffer.append("' AND A.timeLimit='" + timeLimit + "' GROUP BY B.primaryKey  ORDER BY A.addTime desc");
		Session session = this.openSession();
		SQLQuery query = session.createSQLQuery(selectBuffer.toString());
		query.setResultTransformer(new AliasToBeanResultTransformer(ProVideoInfo.class));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		StringBuffer countBuffer = new StringBuffer();
		countBuffer.append(
				" select count(*) from ( SELECT count(*) from ProVideoReward B LEFT JOIN ProVideoInfo A ON A.primaryKey = B.videoId ");
		countBuffer.append(" WHERE B.userId='");
		countBuffer.append(userId);
		countBuffer.append("' and A.type='");
		countBuffer.append(type);
		countBuffer.append("' AND A.timeLimit='" + timeLimit + "' GROUP BY B.primaryKey) T");
		pagination.setTotal(Long.valueOf(session.createSQLQuery(countBuffer.toString()).uniqueResult().toString()));
		@SuppressWarnings("unchecked")
		List<ProVideoInfo> list = query.list();
		pagination.setList(list);
		pagination.setTotal((long) list.size());
		closeSession(session);
		return pagination;
	}

	public void updateGrabThe(String grabThe, String primaryKey) {
		String sql = "update ProVideoInfo set grabThe='" + grabThe + "' where primaryKey='" + primaryKey + "'";
		Session session = this.openSession();
		Query query = session.createQuery(sql);
		query.executeUpdate();
		closeSession(session);
	}
	

	public void updateRedEnvelopResidue(String primaryKey) {
		String sql = "update ProVideoInfo set redEnvelopResidue=redEnvelopTotal-"
				+ "(select sum(money) from ProVideoReward where userId IS NOT NULL and videoId='" + primaryKey + "'), "
				+ "driverPlayNum=(select count(money) from ProVideoReward where userId IS NOT NULL and videoId='" + primaryKey + "')"
				+ " where primaryKey='" + primaryKey + "'";
		Session session = this.openSession();
		Query query = session.createQuery(sql);
		query.executeUpdate();
		closeSession(session);
	}

	public void updateTimeLimit(String date) {
		String sql = "update ProVideoInfo set timeLimit='" + TextUtil.booleanToString(true)
				+ "' where startDate<=str_to_date('" + date + "','%Y-%m-%d') and stopDate>str_to_date('" + date
				+ "','%Y-%m-%d') and timeLimit='" + TextUtil.booleanToString(false) + "'";
		Session session = this.openSession();
		Query query = session.createQuery(sql);
		query.executeUpdate();
		closeSession(session);

		String sql2 = "update ProVideoInfo set timeLimit='" + TextUtil.booleanToString(false)
				+ "' where startDate>str_to_date('" + date + "','%Y-%m-%d') or stopDate<=str_to_date('" + date
				+ "','%Y-%m-%d') and timeLimit='" + TextUtil.booleanToString(true) + "'";
		Session session2 = this.openSession();
		Query query2 = session2.createQuery(sql2);
		query2.executeUpdate();
		closeSession(session2);

	}

	public PaginationFrom<ProVideoInfo> findListByUser(String userId, String timeLimit,
			PaginationFrom<ProVideoInfo> pagination) {
		String sql = "SELECT A.primaryKey,A.videoName,A.videoThumUrl,A.startDate,A.stopDate,CONVERT(SUM(C.playedTime),SIGNED) playedTime,A.redEnvelopTotal FROM ProVideoInfo A LEFT JOIN ProOwnerUser B ON A.userId=B.primaryKey "
				+ "LEFT JOIN ProVideoReward C ON A.primaryKey=C.videoId where A.timeLimit='" + timeLimit + "' "
				+ (timeLimit.equals("0") ? " and A.stopDate<SYSDATE() " : "") + " and B.primaryKey='" + userId
				+ "' GROUP BY A.primaryKey order by A.addTime desc";
		Session session = this.openSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(new AliasToBeanResultTransformer(ProVideoInfo.class));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		String countsql = "SELECT count(*) FROM ProVideoInfo A LEFT JOIN ProOwnerUser B ON A.userId = B.primaryKey "
				+ " where A.timeLimit='" + timeLimit + "' and B.primaryKey='" + userId + "'";
		pagination.setTotal(Long.valueOf(session.createSQLQuery(countsql).uniqueResult().toString()));
		@SuppressWarnings("unchecked")
		List<ProVideoInfo> list = query.list();
		pagination.setList(list);
		closeSession(session);
		return pagination;
	}
}